﻿Imports System.Configuration
Imports Newtonsoft.Json
Imports DatabaseAccess.DBUtility

Public Class uLMSSendDataToWmsBusiness
    Dim ToAppID_WMS As String = ConfigurationManager.AppSettings("ToAppID_WMS") 'add zhunan 2025.9.16

    ''' <summary>
    ''' 保存领料申请json到数据库
    ''' </summary>
    ''' <param name="dic"></param>
    ''' <remarks></remarks>
    Sub SaveMaterialApplyJson(ByVal dic As Dictionary(Of String, Object))
        Try
            If dic("isselfmfg").ToString() <> "0" Then
                Exit Sub
            End If
            Dim togetherflag As String = dic("togetherflag").ToString()
            Dim querySql = <string>
SELECT f.factoryname,u.attributevalue factorycode,mfg.mfgordername,c.togetherflag,c.containerid,c.containername,w.workcentername,
       to_char(c.plannedstartdate,'yyyy-mm-dd') plannedstartdate,
       to_char(c.originalstartdate,'yyyy-mm-dd hh24:mi:ss') originalstartdate,
       p.charactertype,
       pb.productname,p.description,p.productrevision,p.materialcode,p.materialsize,um.uomname,s.specno,s.opdesc,c.qty
FROM container c
LEFT JOIN factory f ON f.factoryid = c.originalfactoryid
LEFT JOIN userattribute u ON u.parentid = f.factoryid AND u.userattributename = '分厂代码'
LEFT JOIN mfgorder mfg ON mfg.mfgorderid = c.mfgorderid
LEFT JOIN workcenter w ON w.workcenterid = c.workcenterid
LEFT JOIN product p ON p.productid = c.productid
LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid
LEFT JOIN uom um ON um.uomid = c.uomid
LEFT JOIN currentstatus cu ON c.currentstatusid = cu.currentstatusid
LEFT JOIN spec s ON s.specid = cu.specid
WHERE c.togetherflag = '{0}' AND c.parentcontainerid IS NULL
ORDER BY c.containername
</string>.Value

            querySql = String.Format(querySql, togetherflag)

            Dim dtContainerInfo = DatabaseHelper.Query_ReturnDatTable(querySql)

            For Each row As DataRow In dtContainerInfo.Rows
                Dim para As Dictionary(Of String, Object) = New Dictionary(Of String, Object)
                para("reqDept") = row("factorycode").ToString() '提请部门,分厂代码【必填】
                para("requester") = dic("fullname") '提请人【必填】
                para("bizDataNo") = row("mfgordername").ToString() '生产订单号【必填】

                Dim bomSql = <string>
                    SELECT erp.id,erp.createdate,erp.mfgordername,erp.productbatchno,erp.productname,
                           erp.qty,erp.uom,erp.specno,erp.rspos,erp.rsnum,erp.specname,erp.schgt,erp.dumps,erp.bomid,erp.lgort,erp.lifnr 
                    FROM erp_send_material erp 
                    WHERE erp.mfgordername='{0}'
                </string>.Value
                Dim dtBom = DatabaseHelper.Query_ReturnDatTable(String.Format(bomSql, row("mfgordername")))

                para("docNo") = IIf(dtBom.Rows.Count > 0, dtBom(0)("rsnum").ToString(), "") '业务流水号(ERP提供)【必填】
                para("batchNum") = row("containername").ToString() '批次号【必填】
                para("opNumber") = "" '工序号
                para("primaryUnit") = row("workcentername").ToString() '主制单元（工区）【必填】
                para("rdDate") = row("plannedstartdate").ToString() '需求送达日期(批次计划开始时间)【必填】
                para("OriginalStartDate") = row("originalstartdate").ToString() '批次创建时间

                '预留字段1-10
                para("userDef01") = "0" '0-计划内；1-计划外
                para("userDef02") = row("charactertype").ToString() '件号【特性分类】
                para("userDef03") = ""
                para("userDef04") = ""
                para("userDef05") = ""
                para("userDef06") = ""
                para("userDef07") = ""
                para("userDef08") = ""
                para("userDef09") = ""
                para("userDef10") = ""

                Dim dtItemList = New DataTable()
                dtItemList.Columns.Add("lineNo", GetType(String)) '业务流水行项目号
                dtItemList.Columns.Add("batchNum", GetType(String)) '批次号
                dtItemList.Columns.Add("itemCode", GetType(String)) '物料编码
                dtItemList.Columns.Add("itemName", GetType(String)) '物料名称
                dtItemList.Columns.Add("modelSpecs", GetType(String)) '规格型号
                dtItemList.Columns.Add("unit", GetType(String)) '计量单位
                dtItemList.Columns.Add("qty", GetType(Int32)) '需求数量
                dtItemList.Columns.Add("remarks", GetType(String)) '备注
                dtItemList.Columns.Add("LGORT", GetType(String)) '库存地点
                dtItemList.Columns.Add("CHARG", GetType(String)) '物料批次
                dtItemList.Columns.Add("LIFNR", GetType(String)) '供应商编码

                dtItemList.Columns.Add("userDef01", GetType(String))
                dtItemList.Columns.Add("userDef02", GetType(String))
                dtItemList.Columns.Add("userDef03", GetType(String))
                dtItemList.Columns.Add("userDef04", GetType(String))
                dtItemList.Columns.Add("userDef05", GetType(String))
                dtItemList.Columns.Add("userDef06", GetType(String))
                dtItemList.Columns.Add("userDef07", GetType(String))
                dtItemList.Columns.Add("userDef08", GetType(String))
                dtItemList.Columns.Add("userDef09", GetType(String))
                dtItemList.Columns.Add("userDef10", GetType(String))

                For Each bomrow In dtBom.Rows
                    Dim dr As DataRow = dtItemList.NewRow()
                    dr("lineNo") = bomrow("rspos") '业务流水行项目号【必填】
                    dr("batchNum") = row("containername").ToString().Split("/")(1) '批次号
                    dr("itemCode") = bomrow("productname").ToString() '物料编码(MaterialCode)【必填】
                    Dim productSql = <string>
                                    SELECT p.description,p.productrevision,p.materialcode,p.materialsize
                                    FROM product p
                                    LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid
                                    WHERE pb.productname = '{0}'
                                 </string>.Value
                    Dim dtProductInfo = DatabaseHelper.Query_ReturnDatTable(String.Format(productSql, bomrow("productname")))

                    If dtProductInfo.Rows.Count > 0 Then
                        dr("itemName") = dtProductInfo.Rows(0)("description").ToString() '物料名称【必填】
                        dr("modelSpecs") = dtProductInfo.Rows(0)("materialsize").ToString() '规格型号（件号：毛料规格）
                    Else
                        dr("itemName") = "" '物料名称【必填】
                        dr("modelSpecs") = "" '规格型号（件号：毛料规格）
                    End If

                    dr("LGORT") = bomrow("lgort").ToString() '库存地点
                    dr("CHARG") = bomrow("productbatchno").ToString() '物料批次
                    dr("LIFNR") = bomrow("lifnr").ToString() '供应商编码
                    dr("unit") = bomrow("uom").ToString() '计量单位(ERP)
                    dr("qty") = IIf(bomrow("qty").ToString() = "", 0, Convert.ToInt32(bomrow("qty"))) '需求数量【必填】
                    dr("remarks") = "" '备注

                    '预留字段1-10
                    dr("userDef01") = ""
                    dr("userDef02") = ""
                    dr("userDef03") = ""
                    dr("userDef04") = ""
                    dr("userDef05") = ""
                    dr("userDef06") = ""
                    dr("userDef07") = ""
                    dr("userDef08") = ""
                    dr("userDef09") = ""
                    dr("userDef10") = ""
                    dtItemList.Rows.Add(dr)
                Next

                para("itemList") = dtItemList

                Dim jsonResult As String = JsonConvert.SerializeObject(para)
                Dim insertSql = <string>
INSERT INTO sync_sendto_wms(methodname,json,toappid,tablename,uniquecolname,uniquecolvalue,create_emp) 
VALUES('领料单下发','{0}','{1}','container','containerid','{2}','{3}')</string>.Value
                insertSql = String.Format(insertSql, jsonResult, ToAppID_WMS, row("containerid"), dic("employeeid"))
                DatabaseHelper.ExecuteSql(insertSql)
            Next
        Catch ex As Exception
        End Try
    End Sub

End Class
